#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "

-- 考勤人数统计表
-- etc1 简单筛选字段，数据，并打标记
INSERT INTO edu_dta.dta_signin_count
WITH temp1 AS (
SELECT
    normal_class_flag,
    class_id,
    student_id,
    signin_time,
    signin_date,
    share_state,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    use_begin_date,
    use_end_date,
    class_date,
    content,
    -- 出勤判断
    -- 满足早上正常出勤为1，下午正常出勤为2，晚上正常出勤为3
    CASE
    WHEN cast(signin_time as time) >= date_add('minute', -40, cast(morning_begin_time as time))
        AND cast(signin_time as time) <= cast(morning_end_time as time)
    THEN 1      -- 上午
    WHEN cast(signin_time as time) >= date_add('minute', -40, cast(afternoon_begin_time as time))
        AND cast(signin_time as time) <= cast(afternoon_end_time as time)
    THEN 2      -- 下午
    WHEN cast(signin_time as time) >= date_add('minute', -40, cast(evening_begin_time as time))
        AND cast(signin_time as time) <= cast(evening_end_time as time)
    THEN 3      -- 晚上
    ELSE 0
    END AS signin_between

FROM edu_dws.t_signin_detail
WHERE share_state = 1 AND class_date BETWEEN use_begin_date AND use_end_date),
temp2 as (
    SELECT
        class_date,
        class_id,
        student_id,
        signin_time,
        morning_begin_time,
        morning_end_time,
        afternoon_end_time,
        afternoon_begin_time,evening_begin_time,
        evening_end_time,
        signin_between,
        row_number() over(PARTITION BY class_date,class_id,student_id,signin_between ORDER BY signin_time) AS signin_rn
    FROM temp1
    WHERE signin_between <> 0
),
temp3 AS (
    SELECT
        class_date,
        class_id,
        student_id,
     CASE
        WHEN cast(signin_time as time) >= date_add('minute', -40, cast(morning_begin_time as time))
            AND cast(signin_time as time) <= date_add('minute', 10, cast(morning_begin_time as time))
        THEN 1
        WHEN cast(signin_time as time) >= date_add('minute', -40, cast(afternoon_begin_time as time))
            AND cast(signin_time as time) <= date_add('minute', 10, cast(afternoon_begin_time as time))
        THEN 2
        WHEN cast(signin_time as time) >= date_add('minute', -40, cast(evening_begin_time as time))
            AND cast(signin_time as time) <= date_add('minute', 10, cast(evening_begin_time as time))
        THEN 3
        WHEN cast(signin_time as time) >= date_add('minute', 10, cast(morning_begin_time as time))
            AND cast(signin_time as time) <= cast(morning_end_time as time)
        THEN -1
        WHEN cast(signin_time as time) >= date_add('minute', 10, cast(afternoon_begin_time as time))
            AND cast(signin_time as time) <= cast(afternoon_end_time as time)
        THEN -2
        WHEN cast(signin_time as time) >= date_add('minute', 10, cast(evening_begin_time as time))
            AND cast(signin_time as time) <= cast(evening_end_time as time)
        THEN -3
        ELSE 0
        END AS signin_type
    FROM temp2
    WHERE signin_rn = 1),
temp4 AS (
SELECT
    class_date,
    class_id,
    student_id,
    signin_type,
    COUNT(student_id) AS stu_count
FROM temp3
group BY class_date, class_id, signin_type),
temp5 AS (
SELECT
    class_date,
    class_id,
    CASE WHEN signin_type = 1 THEN stu_count ELSE 0 END AS morning_signin_count,
    CASE WHEN signin_type = 2 THEN stu_count ELSE 0 END AS afternoon_signin_count,
    CASE WHEN signin_type = 3 THEN stu_count ELSE 0 END AS evening_signin_count,
    CASE WHEN signin_type = -1 THEN stu_count ELSE 0 END AS morning_late_count,
    CASE WHEN signin_type = -2 THEN stu_count ELSE 0 END AS afternoon_late_count,
    CASE WHEN signin_type = -3 THEN stu_count ELSE 0 END AS evening_late_count
FROM temp4)
SELECT class_date,
       class_id,
       max(morning_signin_count) AS morning_signin_count,
       max(afternoon_signin_count) AS afternoon_signin_count,
       max(evening_signin_count) AS evening_signin_count,
       max(morning_late_count) AS morning_late_count,
       max(afternoon_late_count) AS afternoon_late_count,
       max(evening_late_count) AS evening_late_count
FROM temp5
GROUP BY class_date, class_id;





-- 请假人数统计表
INSERT INTO edu_dta.dta_leave_result_count
-- etc1 简单帅选字段，数据
WITH temp1 AS (
SELECT
    ddctud.class_date AS class_date,
    ld.class_id AS class_id,
    student_id,
    begin_time,
    begin_time_type,
    end_time,
    end_time_type,
    days,
    studying_student_count,
    morning_begin_time,
    afternoon_begin_time,
    evening_begin_time
FROM edu_dws.t_leave_detail AS ld
JOIN edu_dwd.dwd_dt_course_table_upload_detail AS ddctud
ON ld.class_id = ddctud.class_id
WHERE ddctud.class_date >= substr(ld.begin_time, 1, 10) AND ddctud.class_date <= substr(ld.end_time, 1, 10)
    AND leave_type = 1 AND cancel_state = 0 AND valid_state = 1),
-- etc2 给数据打标记
temp2 AS (
SELECT
    class_date,
    class_id,
    student_id,
    -- 请假时间段判断
    -- 满足早上请假为1，下午请假为2，晚上请假为3
    CASE
        WHEN morning_begin_time >= substr(begin_time,12,8) AND morning_begin_time <= substr(end_time,12,8)
        THEN 1
        ELSE NULL
        END AS leave_morning,

    CASE
        WHEN afternoon_begin_time >= substr(begin_time,12,8) AND afternoon_begin_time <= substr(end_time,12,8)
        THEN 1
        ELSE NULL
        END AS leave_afternoon,

    CASE
        WHEN afternoon_begin_time >= substr(begin_time,12,8) AND afternoon_begin_time <= substr(end_time,12,8)
        THEN 1
        ELSE NULL
        END AS leave_evening,

        row_number() over(PARTITION BY class_date,class_id,student_id) AS leave_rn
FROM temp1)
-- 人数计算
SELECT
    class_date,
    class_id,
    leave_type,
    -- 统计早上请假人数
    COUNT(leave_morning)  AS morning_leave_count,
    -- 统计下午请假人数
    COUNT(leave_afternoon)  AS afternoon_leave_count,
    -- 统计晚上请假人数
--     COUNT(if(leave_type = 3, 1, NULL))  AS evening_leave_count,
    count(leave_evening)  AS evening_leave_count
FROM temp2
WHERE leave_rn = 1
GROUP BY class_date, class_id;



-- 旷课人数表
INSERT INTO edu_dta.dta_absent_count
WITH temp1 AS (
SELECT
    sc.class_date AS class_date,
    sc.class_id AS class_id,
    COALESCE(sc.morning_signin_count,0) AS morning_signin_count,
    COALESCE(sc.afternoon_signin_count,0) AS afternoon_signin_count,
    COALESCE(sc.evening_signin_count,0) AS evening_signin_count,
    COALESCE(sc.morning_late_count,0) AS morning_late_count,
    COALESCE(sc.afternoon_late_count,0) AS afternoon_late_count,
    COALESCE(sc.evening_late_count,0) AS evening_late_count,
    COALESCE(lc.morning_leave_count,0) AS morning_leave_count,
    COALESCE(lc.afternoon_leave_count,0) AS afternoon_leave_count,
    COALESCE(lc.evening_leave_count,0) AS evening_leave_count,
    cssc.studying_student_count AS class_student_count
FROM edu_dta.dta_signin_count AS sc
full JOIN edu_dta.dta_leave_count AS lc
ON sc.class_date = lc.class_date AND sc.class_id = lc.class_id
left JOIN edu_dwd.dwd_dt_class_studying_student_count AS cssc
ON sc.class_id = cssc.class_id and sc.class_date=cssc.studying_date)
SELECT
    class_date,
    class_id,
    (((class_student_count - morning_signin_count) - morning_late_count) - morning_leave_count) AS morning_absent_count,
    (((class_student_count - afternoon_signin_count) - afternoon_late_count) - afternoon_leave_count) AS afternoon_absent_count,
    (((class_student_count - evening_signin_count) - evening_late_count) - evening_leave_count) AS evening_absent_count
FROM temp1;
"